if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ExecuteCOIOpenEnrollment]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ExecuteCOIOpenEnrollment]
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO 

/******************************************************************                
* Name:   ExecuteCOIOpenEnrollment                
* Purpose:  Notify/Remind users to fill out COIs                
*                
* PARAMETERS                
* Name        Description                     
* -------------     -------------------------------------------                
*  @type               Type of notification      
*  @institutionId      Institution under which the users have to be notified   
*  @lastActivityDate   Last activity date(for open enrollment)            
******************************************************************/                
                
Create Procedure [dbo].[ExecuteCOIOpenEnrollment]
(               
 @institutionId int = 0,              
 @lastActivityDate Datetime = NUll,
 @type varchar(50),
 @reportingPeriodId int
)                
AS                
BEGIN                
                
 SET NOCOUNT ON                
 --Create the temporary table to hold the list of users having having access to COI               
 DECLARE @TempPersonIds TABLE (                
 [RowNum] INT IDENTITY (1,1),                
 [Id] INT                
 )                
 --Total person count to loop through                 
 DECLARE @personCount INT    
 -- Loop index
 DECLARE @recordIndex INT
 --flag that tells whether or not the activity date is selected       
 Declare @isLastActivity bit       
     
 --Set the last activity flag
  if @lastActivityDate is NUll      
 BEGIN              
 Set @lastActivityDate =GetDate()      
 Set @isLastActivity = 0       
 END      
 Else      
 Set @isLastActivity =1             
  
 /*If institution is selected(Note: This option will enable the the superuser in the application 
  to send out open enrollment for the selected institution )*/
 if @institutionId != 0              
  BEGIN   
	   --Table to hold the institution,Department and unit Ids for the selected institution. 
	   DECLARE @TempInstitutionIds TABLE (                
	   [Id] INT,              
	   [Name] Varchar(500)              
	   )                
	   --Fill the institution table 
	   Insert Into @TempInstitutionIds(Id,[Name]) Select Id as [InstitutionId],[Name]         
		from fwkDomainOrganization where isnull(institutionId,1)=@institutionId 
		--EXEC [dbo].GetInstitutionOrUnitOrDept @institutionId             
        
	   --Get the primary members associated with the organization(joining with AdmInternalPerson)              
	   Insert @TempPersonIds(Id)                
	   SELECT DISTINCT adp.Id               
		FROM AdmPerson adp              
		INNER JOIN AdmInternalPerson aip              
		 On adp.Id = aip.AdmPersonId              
		INNER JOIN (Select Distinct Id from @TempInstitutionIds) tmpOId              
		 On tmpOId.Id = aip.PrimaryOrganizationId                
		INNER JOIN FwkDomainUser FDU               
		 ON adp.FwkDomainUserId = FDU.Id                
		INNER JOIN FwkApplicationUser FAU               
		 ON FDU.Id = FAU.FwkDomainUserId              
		WHERE ModuleBitMask & 128 > 0               
		UNION              
	   --Get the Secondary members associated with the organization(joining with Admperson_FwkDomainOrganization_Map)              
		SELECT DISTINCT adp.Id              
		FROM AdmPerson adp              
		INNER JOIN Admperson_FwkDomainOrganization_Map afm              
		 On adp.Id = afm.AdmPersonId              
		INNER JOIN (Select Distinct Id from @TempInstitutionIds) tmpOId              
		 On tmpOId.Id = afm.FwkDomainOrganizationId                
		INNER JOIN FwkDomainUser FDU               
		 ON adp.FwkDomainUserId = FDU.Id                
		INNER JOIN FwkApplicationUser FAU               
		 ON FDU.Id = FAU.FwkDomainUserId              
		WHERE ModuleBitMask & 128 > 0              
	              
  END              
 Else 
	--If the institution is not selected then get the user whoever has access COI access.            
  BEGIN              
	   INSERT @TempPersonIds (Id) 
		-- Get the user whoever has access COI access.                
		 (SELECT DISTINCT AdmPerson.Id                 
		 FROM AdmPerson INNER JOIN FwkDomainUser FDU ON AdmPerson.FwkDomainUserId = FDU.Id                
		INNER JOIN FwkApplicationUser FAU ON FDU.Id = FAU.FwkDomainUserId                
		 WHERE ModuleBitMask & 128 > 0 
	                   
		 UNION                
	   -- Get the users in the hierarchy
		 SELECT DISTINCT ChildId                 
		 FROM AdmPerson INNER JOIN COI_Hierarchy_Map Map ON AdmPerson.Id = Map.ChildId                
		 WHERE Map.Removed = 0)                
 END 

 --Table to store the admpersonIds who has to be notified 
 DECLARE @TempEmailPersonIds TABLE (                
 [Id] INT,
 [AddCOI] bit
 )      
 --Select * from @TempPersonIds            
	IF @type = 'COINTFNTYPE_OPNENRLMNT'                
	BEGIN                
                 
		   --Add COIs (If Start or InProgress COI exist, then update Notified Date. If submitted COI exist, then clone. Else add a new COI)                
		   SELECT @personCount = count(*) FROM @TempPersonIds                
		   DECLARE @admPersonId INT                
		   DECLARE @coiId INT                
		   SET @recordIndex = 1            
		   WHILE (@recordIndex <= @personCount)                
			BEGIN -- for each admperson id in temp table                
		                    
			   SELECT @admPersonId = Id FROM @TempPersonIds WHERE RowNum = @recordIndex                
			   Set @coiId = 0               
			   --Get Start or InProgress coi id                
			   SET @coiId = (SELECT max(Id) FROM ConflictOfInterest                
			   WHERE status in ('COISTATUS_START', 'COISTATUS_INPROGRESS')                
			   AND admpersonid = @admPersonId)                
			                      
				IF (isnull(@coiId, 0) = 0)                 
					 BEGIN                
							 --Get Submitted COI id                
							 SET @coiId = (SELECT max(Id) FROM ConflictOfInterest                
							 WHERE status in ('COISTATUS_SUBMITTED', 'COISTATUS_REVIEWED')                
							 AND admpersonid = @admPersonId)                
				           
						IF (isnull(@coiId, 0) = 0)                 
							BEGIN                
								  Insert into @TempEmailPersonIds values (@admPersonId, 1)               
							END                  
						ELSE                
							BEGIN                
								DECLARE @dateSubmitted DateTime               
								Select @dateSubmitted = Isnull(DateSubmitted,'1/1/1900') from conflictOfInterest where Id = @coiId              
								if @lastActivityDate > @dateSubmitted              
									Begin              
										Insert into @TempEmailPersonIds values (@admPersonId, 1)              
									End               
							END                       
					END            
				ELSE               
					BEGIN       
						if @isLastActivity = 0  							   
							 UPDATE ConflictOfInterest SET LastNotified = GetDate() WHERE Id = @coiId    
						else if  (@isLastActivity <> 0 and @lastActivityDate >= (Select Isnull(DateCreated,'1/1/1900') from conflictOfInterest where Id = @coiId ))    
							  BEGIN    
								UPDATE ConflictOfInterest SET LastNotified = GetDate() WHERE Id = @coiId 
							 END      
							Insert into @TempEmailPersonIds values (@admPersonId, 0)
					END                    
			   SET @coiId = 0                     
			   SET @recordIndex = @recordIndex + 1                
			 END                   
	END                
	ELSE                
		 BEGIN        
			DECLARE @Status varchar(100)
			SET @Status =	CASE WHEN @Type LIKE 'COINTFNTYPE_%_START' THEN 'COISTATUS_START'
								WHEN @Type LIKE 'COINTFNTYPE_%_INPROGRESS' THEN 'COISTATUS_INPROGRESS'
							END
			Insert @TempEmailPersonIds(Id, AddCOI)              
			   (SELECT DISTINCT tmp.Id, 0 FROM @TempPersonIds tmp               
			   INNER JOIN                 
			   ConflictOfInterest COI ON tmp.Id = COI.AdmPersonId                
			   AND COI.Status = @Status               
			   )              
				UPDATE ConflictOfInterest SET LastNotified = GetDate()                
				WHERE status = @Status               
				AND admpersonid IN ( Select Distinct(Id) from @TempEmailPersonIds)                 
		 END                  
           
                
   Select distinct Id, AddCOI from @TempEmailPersonIds              
              
                  
END
GO
